﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using ZLDC.Common.Estate;

namespace ZLDC.DAL.Estate
{
    public class HousingKeyDAL
    {
        public static void p_housingKey_save(int ID, int housingId, string KeyNo, int borrowerDeptID, string borrowerDept, int borrowerID, string borrower, int receiverID, string receiver, string Operator, string revertDate, string borrowDate, out string outmsg, out int newid, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@ID", ID));
            parms.Add(new SqlParameter("@housingId", housingId));
            parms.Add(new SqlParameter("@KeyNo", KeyNo));
            parms.Add(new SqlParameter("@borrowerDeptID", borrowerDeptID));
            parms.Add(new SqlParameter("@borrowerDept", borrowerDept));
            parms.Add(new SqlParameter("@borrowerID", borrowerID));
            parms.Add(new SqlParameter("@borrower", borrower));
            parms.Add(new SqlParameter("@receiverID", receiverID));
            parms.Add(new SqlParameter("@receiver", receiver));
            parms.Add(new SqlParameter("@operator", Operator));
            parms.Add(new SqlParameter("@revertDate", revertDate));
            parms.Add(new SqlParameter("@borrowDate", borrowDate));

            SqlParameter pOutmsg = new SqlParameter("@outmsg", SqlDbType.VarChar, 256);
            pOutmsg.Direction = ParameterDirection.Output;
            parms.Add(pOutmsg);

            SqlParameter pNewid = new SqlParameter("@newid", SqlDbType.Int, 4);
            pNewid.Direction = ParameterDirection.Output;
            parms.Add(pNewid);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);


            db.execute_procedure("p_housingKey_save", parms);

            outmsg = (pOutmsg.Value == System.DBNull.Value) ? "" : Convert.ToString(pOutmsg.Value);
            newid = (pNewid.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pNewid.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

        }
        public static DataTable p_housingKey_list_table(int page, int pagesize, int housingid, ref int total, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@page", page));
            parms.Add(new SqlParameter("@pagesize", pagesize));
            parms.Add(new SqlParameter("@housingid", housingid));

            SqlParameter pTotal = new SqlParameter("@total", SqlDbType.Int, 4);
            pTotal.Direction = ParameterDirection.InputOutput;
            pTotal.Value = total;
            parms.Add(pTotal);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);

            DataTable dt = null;
            dt = db.GetDataTable("p_housingKey_list", parms);

            total = (pTotal.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pTotal.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

            return dt;
        }
                public static string SelectKeyNoByHId(string id)
        {
            DataSet ds = null;

            string keyno = "";

            using (_Database db = new _Database("estate"))
            {                
                string sql = "select * from t_housing where id='" + id + "'";
                ds = db.Query(sql);
                if (ds != null)
                {
                    keyno = ds.Tables[0].Rows[0]["keyNo"].ToString();
                }
            }

            return keyno;
        }
                public static DataTable p_housing_Key_Report_list_table(int page, int pagesize, string condition, ref int total, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@page", page));
            parms.Add(new SqlParameter("@pagesize", pagesize));
            parms.Add(new SqlParameter("@condition", condition));

            SqlParameter pTotal = new SqlParameter("@total", SqlDbType.Int, 4);
            pTotal.Direction = ParameterDirection.InputOutput;
            pTotal.Value = total;
            parms.Add(pTotal);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);

            DataTable dt = null;
            dt = db.GetDataTable("p_housing_Key_Report_list", parms);

            total = (pTotal.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pTotal.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

            return dt;
        }
    }
}
